//
//  EVEdb.m
//  EVEdb
//
//  Created by Scott Guyer on 3/31/08.
//  Copyright 2008 __MyCompanyName__. All rights reserved.
//

#import "InventoryItem.h"
#import "Skill.h"
#import "EVEdb.h"


@implementation EVEdb

static sqlite3*         db = nil;
static sqlite3_stmt*    inventorySql = nil;
static sqlite3_stmt*    groupListSql = nil;
static sqlite3_stmt*    allSkillsSql = nil;
static sqlite3_stmt*    groupSkillsSql = nil;
static sqlite3_stmt*    skillSql = nil;


+ (BOOL) openDatabase
{
	if ( db != nil )
		return YES;
		
//    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
//    NSString *documentsDirectory = [paths objectAtIndex:0];
//    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"eve.db"];
	
	NSString *path = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"eve.db"];

	int rc = sqlite3_open( [path UTF8String], &db);
	if ( rc != SQLITE_OK )
	{
		NSLog(@"Unable to open database: %s", sqlite3_errmsg(db));
		return NO;
	}
	
	return YES;
}



+ (void) closeDatabase
{
	
	if ( inventorySql )
	{
		sqlite3_finalize( inventorySql);
		inventorySql = nil;
	}
	
	if ( groupListSql )
	{
		sqlite3_finalize( groupListSql);
		groupListSql = nil;
	}

	if ( allSkillsSql )
	{
		sqlite3_finalize( allSkillsSql);
		allSkillsSql = nil;
	}

	if ( groupSkillsSql )
	{
		sqlite3_finalize( groupSkillsSql);
		groupSkillsSql = nil;
	}
	
	if ( skillSql )
	{
		sqlite3_finalize( skillSql);
		skillSql = nil;
	}
	
	
	if ( db )
		sqlite3_close( db);
	
	db = 0;
}




+ (InventoryItem*) createItemFromSql:(sqlite3_stmt*)sql
{

	if ( ! sql )
		return nil;
		
	InventoryItem* item = [[InventoryItem alloc] init];	
		
	item.typeID = sqlite3_column_int( sql, 0);
	item.typeName = [[NSString alloc] initWithCString:(const char*)sqlite3_column_text(sql,1)];
	item.desc = [[NSString alloc] initWithCString:(const char*)sqlite3_column_text(sql,2)];
	
	return item;
}

+ (PilotAttrType) dbAttributeToPilotAttrType:(int)dbType
{
	switch (dbType) 
	{
		case 164:
			return kPilotAttrTypeCharisma;
			break;
		case 165:
			return kPilotAttrTypeIntel;
			break;
		case 166:
			return kPilotAttrTypeMemory;
			break;
		case 167:
			return kPilotAttrTypePerception;
			break;
		case 168:
			return kPilotAttrTypeWillpower;
			break;						
		default:			
			break;
	}
	
	return 0;	
}

+ (Skill*) createSkillFromSql:(sqlite3_stmt*)sql
{
	
	if ( ! sql )
		return nil;
	
	Skill* item = [[Skill alloc] init];	
	
	// values are in this order...
	// typeID,typeName,description,groupID,groupName,rank,primaryAttribute,secondaryAttribute
	
	item.typeID = sqlite3_column_int( sql, 0);
	item.name = [NSString stringWithCString:(const char*)sqlite3_column_text(sql,1)];
	item.description = [NSString stringWithCString:(const char*)sqlite3_column_text(sql,2)];
	item.groupName = [NSString stringWithCString:(const char*)sqlite3_column_text(sql,4)];
	item.rank = sqlite3_column_double( sql, 5);
	item.primaryAttribute = [self dbAttributeToPilotAttrType:sqlite3_column_int( sql,6)];
	item.secondaryAttribute = [self dbAttributeToPilotAttrType:sqlite3_column_int( sql,7)];	
	
	return item;
}


+ (InventoryItem*) getItemWithKey:(int)pk 
{
	int rc = SQLITE_OK;
	
	if ( db == nil )
	{
		if ( ! [self openDatabase] )
		{
			NSLog(@"%s unable to open database", __FUNCTION__);
			return nil;
		}
	}
	

	if ( inventorySql == nil )
	{
		int rc = SQLITE_OK;
		NSString* sqlStmt = [[NSString alloc] initWithString:@"select typeID,typeName,description from skills WHERE typeID=?"];		
		
		// Compile SQL...
		rc = sqlite3_prepare_v2( db, [sqlStmt UTF8String], -1, &inventorySql, NULL);
		[sqlStmt release];	
		
		if ( rc != SQLITE_OK )
		{	
			NSLog(@"problem compiling SQL: %s", sqlite3_errmsg(db));
			inventorySql = nil; // a bit hopeful ;-)
			return nil;
		}		
	}
	
	if ( sqlite3_bind_int(inventorySql, 1, pk) != SQLITE_OK )
	{
		NSLog(@"error binding parameter: %s", sqlite3_errmsg(db));
		sqlite3_reset( inventorySql);
		return nil;
	}
	

	// Get results...
	if ( (rc = sqlite3_step( inventorySql)) != SQLITE_ROW )
	{
		NSLog(@"no results");
		sqlite3_reset( inventorySql);
		return nil;
	}
		
	InventoryItem* result = [self createItemFromSql:inventorySql];	
	
	sqlite3_reset( inventorySql);
	
	return result;
}


+ (Skill*) getSkillWithId:(int)id 
{
	int rc = SQLITE_OK;
	
	if ( db == nil )
	{
		if ( ! [self openDatabase] )
		{
			NSLog(@"%s unable to open database", __FUNCTION__);
			return nil;
		}
	}
	
	
	if ( skillSql == nil )
	{
		int rc = SQLITE_OK;
		NSString* sqlStmt = [[NSString alloc] initWithString:@"select typeID,typeName,description,groupID,groupName,rank,primaryAttribute,secondaryAttribute from skills WHERE typeID=?"];		
		
		// Compile SQL...
		rc = sqlite3_prepare_v2( db, [sqlStmt UTF8String], -1, &skillSql, NULL);
		[sqlStmt release];	
		
		if ( rc != SQLITE_OK )
		{	
			NSLog(@"problem compiling SQL: %s", sqlite3_errmsg(db));
			skillSql = nil; // a bit hopeful ;-)
			return nil;
		}		
	}
	
	if ( sqlite3_bind_int(skillSql, 1, id) != SQLITE_OK )
	{
		NSLog(@"error binding parameter: %s", sqlite3_errmsg(db));
		sqlite3_reset( skillSql);
		return nil;
	}
	
	
	// Get results...
	if ( (rc = sqlite3_step( skillSql)) != SQLITE_ROW )
	{
		NSLog(@"no results");
		sqlite3_reset( skillSql);
		return nil;
	}
	
	Skill* result = [self createSkillFromSql:skillSql];	
	
	sqlite3_reset( skillSql);
	
	return result;
}



+ (NSDictionary*) findItemsWithPhrase:(NSString*)phrase
{
    int rc=0;
	sqlite3_stmt* stmt = 0;
	NSMutableDictionary* results = [[NSMutableDictionary alloc] initWithCapacity:16];
	
	// TODO: modify this to create things on demand (db, and sql)
	// TODO: prepare this statement one time
	
	NSString* sql = [[NSString alloc] 
	                   initWithFormat:@"SELECT typeID,typeName from skills WHERE (typeName LIKE '%%%@%%') OR (description LIKE '%%%@%%')", 
					   phrase, phrase];
	rc = sqlite3_prepare_v2( db, [sql UTF8String], -1, &stmt, NULL);
	[sql release];	
	if ( rc != SQLITE_OK )
	{
		NSLog(@"SQL Error: %s", sqlite3_errmsg(db));		
		return results;
	}


	NSString* name;
	NSNumber* typeid;
	while ( (rc = sqlite3_step(stmt)) == SQLITE_ROW )
	{
		typeid = [NSNumber numberWithInt:sqlite3_column_int(stmt,0)];
		name = [NSString stringWithCString:(const char*)sqlite3_column_text(stmt,1) encoding:NSASCIIStringEncoding];
		[results setValue:typeid forKey:name];
	}
	
	sqlite3_finalize( stmt);
	
	return results;
}


+ (NSDictionary*) getSkillGroups 
{
	int rc = 0;
	
	if ( db == nil )
	{
		if ( ! [self openDatabase] )
		{
			NSLog(@"%s unable to open database", __FUNCTION__);
			return nil;
		}
	}
	
	
	if ( groupListSql == nil )
	{
		rc = SQLITE_OK;
		NSString* sqlStmt = [[NSString alloc] initWithString:@"select distinct groupID,groupName from skills"];		
		
		// Compile SQL...
		rc = sqlite3_prepare_v2( db, [sqlStmt UTF8String], -1, &groupListSql, NULL);
		[sqlStmt release];	
		
		if ( rc != SQLITE_OK )
		{	
			NSLog(@"problem compiling SQL: %s", sqlite3_errmsg(db));
			groupListSql = nil; // a bit hopeful ;-)
			return nil;
		}		
	}
	
	NSMutableDictionary* results = [[NSMutableDictionary alloc] initWithCapacity:16];
	NSString* name;
	NSNumber* typeid;
	while ( (rc = sqlite3_step( groupListSql)) == SQLITE_ROW )
	{
		typeid = [NSNumber numberWithInt:sqlite3_column_int( groupListSql, 0)];
		name   = [NSString stringWithCString:(const char*)sqlite3_column_text( groupListSql,1) encoding:NSASCIIStringEncoding];
		[results setValue:typeid forKey:name];
	}
	
	sqlite3_reset( groupListSql);
	
	return results;
}


+ (NSDictionary*) getSkillsInGroup:(NSUInteger)groupId 
{
	int rc = 0;
	
	if ( db == nil )
	{
		if ( ! [self openDatabase] )
		{
			NSLog(@"%s unable to open database", __FUNCTION__);
			return nil;
		}
	}
	
	
	if ( groupSkillsSql == nil )
	{
		rc = SQLITE_OK;
		NSString* sqlStmt = [[NSString alloc] initWithString:@"select typeID,typeName from skills WHERE groupID=?"];		
		
		// Compile SQL...
		rc = sqlite3_prepare_v2( db, [sqlStmt UTF8String], -1, &groupSkillsSql, NULL);
		[sqlStmt release];	
		
		if ( rc != SQLITE_OK )
		{	
			NSLog(@"problem compiling SQL: %s", sqlite3_errmsg(db));
			groupSkillsSql = nil; // a bit hopeful ;-)
			return nil;
		}		
	}
	
	
	if ( sqlite3_bind_int( groupSkillsSql, 1, groupId) != SQLITE_OK )
	{
		NSLog(@"error binding parameter: %s", sqlite3_errmsg(db));
		sqlite3_reset( groupSkillsSql);
		return nil;
	}
	
	NSMutableDictionary* results = [[NSMutableDictionary alloc] initWithCapacity:16];
	NSString* name;
	NSNumber* typeid;
	while ( (rc = sqlite3_step( groupSkillsSql)) == SQLITE_ROW )
	{
		typeid = [NSNumber numberWithInt:sqlite3_column_int( groupSkillsSql, 0)];
		name   = [NSString stringWithCString:(const char*)sqlite3_column_text( groupSkillsSql,1) encoding:NSASCIIStringEncoding];
		[results setValue:typeid forKey:name];
	}
	
	sqlite3_reset( groupSkillsSql);
	
	return results;	
}



+ (NSMutableDictionary*) getAllSkills 
{
	int rc = 0;
	
	if ( db == nil )
	{
		if ( ! [self openDatabase] )
		{
			NSLog(@"%s unable to open database", __FUNCTION__);
			return nil;
		}
	}
	
	
	if ( allSkillsSql == nil )
	{
		rc = SQLITE_OK;
		NSString* sqlStmt = [[NSString alloc] initWithString:@"SELECT typeID,typeName from skills"];		
		
		// Compile SQL...
		rc = sqlite3_prepare_v2( db, [sqlStmt UTF8String], -1, &allSkillsSql, NULL);
		[sqlStmt release];	
		
		if ( rc != SQLITE_OK )
		{	
			NSLog(@"problem compiling SQL: %s", sqlite3_errmsg(db));
			allSkillsSql = nil; // a bit hopeful ;-)
			return nil;
		}		
	}
	
	
	NSMutableDictionary* results = [[NSMutableDictionary alloc] initWithCapacity:256];
	NSString* name;
	NSNumber* typeid;
	while ( (rc = sqlite3_step( allSkillsSql)) == SQLITE_ROW )
	{
		typeid = [NSNumber numberWithInt:sqlite3_column_int( allSkillsSql, 0)];
		name   = [NSString stringWithCString:(const char*)sqlite3_column_text( allSkillsSql,1) encoding:NSASCIIStringEncoding];
		[results setValue:typeid forKey:name];
	}
	
	sqlite3_reset( allSkillsSql);
	
	return results;	
}




@end
